package com.godenwater.core.spring;

import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.simple.SimpleJdbcInsert;
import org.springframework.jdbc.support.lob.DefaultLobHandler;

public class BaseDao {

    protected DataSource dataSource;

    protected JdbcTemplate jdbcTemplate;

    public JdbcTemplate getJdbcTemplate() {
        return jdbcTemplate;
    }

    protected NamedParameterJdbcTemplate namedJdbcTemplate;

    public NamedParameterJdbcTemplate getNamedJdbcTemplate() {
        return namedJdbcTemplate;
    }

    public BaseDao() {
        super();
    }

    protected DefaultLobHandler lobHandler;

    public BaseDao(DataSource dataSource) {
        this.dataSource = dataSource;
        this.jdbcTemplate = new JdbcTemplate(dataSource);
        this.namedJdbcTemplate = new NamedParameterJdbcTemplate(
                this.jdbcTemplate);
        this.lobHandler = new DefaultLobHandler();

    }

    public void setDataSource(DataSource dataSource) {
        this.dataSource = dataSource;
        // ResultSet.TYPE_SCROLL_SENSITIVE,它只记录行ID，在取真正的数据时，才查询数据
        // conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
        // ResultSet.CONCUR_READ_ONLY);

        this.jdbcTemplate = new JdbcTemplate(dataSource);
        this.namedJdbcTemplate = new NamedParameterJdbcTemplate(
                this.jdbcTemplate);
        this.lobHandler = new DefaultLobHandler();

    }

    public DataSource getDataSource() {
        return this.dataSource;
    }

    /**
     * 插入数据操作，根据表名，列名，参数值将数据写入到数据库中
     *
     * @param tableName
     * @param columnNames
     * @param params
     */
    public void insert(String tableName, String[] columnNames, Map params)
            throws Exception {
        new SimpleJdbcInsert(dataSource).withTableName(tableName).usingColumns(
                columnNames) // .usingGeneratedKeyColumns(tableKeyColumns)
                .execute(params);

    }

    /**
     * 更新数据操作，根据传入的sql及参数，更新数据库操作
     *
     * @param sql
     * @param params
     */
    public void update(String sql, Object[] params) throws DataAccessException {
        if (params == null || params.length == 0) {
            jdbcTemplate.update(sql);
        } else {
            jdbcTemplate.update(sql, params);
            // jdbcTemplate.update(sql, new
            // BasePreparedStatementSetter(params));
        }
    }

    /**
     * 对数据库的 "分页" 操作
     *
     * @param sql
     * @param pageNumber
     * @param pageSize
     * @return
     */
    public List queryForList(String sql)
            throws DataAccessException {

        return jdbcTemplate.queryForList(sql);
    }


    /**
     * 对数据库的 "分页" 操作
     *
     * @param sql
     * @param pageNumber
     * @param pageSize
     * @return
     */
    public List queryForPage(String sql, int pageNumber, int pageSize)
            throws DataAccessException {

        // sql = "select * from (select row_.*, rownum rownum_ from (" + sql
        // + ") row_ where rownum <= " + ((pageNumber + 1) * pageSize)
        // + ") where rownum_ > " + (pageNumber * pageSize);

        sql = "select * from (" + sql + ") t LIMIT " + ((pageNumber - 1) * pageSize)
                + "," + pageSize;

        return jdbcTemplate.queryForList(sql);
    }

    @SuppressWarnings("unchecked")
    public List queryForPage(String sql, Object[] parameters, int pageNumber,
                             int pageSize) throws DataAccessException {

        // sql = "select * from (select row_.*, rownum rownum_ from (" + sql
        // + ") row_ where rownum <= " + ((pageNumber + 1) * pageSize)
        // + ") where rownum_ > " + (pageNumber * pageSize);

        sql = "select * from (" + sql + ") t LIMIT "
                + ((pageNumber - 1) * pageSize) + "," + pageSize;

        return jdbcTemplate.queryForList(sql, parameters);
    }

    public int queryForCount(String sql) throws DataAccessException {
        sql = "select count(*) from (" + sql + ") t";
        return jdbcTemplate.queryForInt(sql);
    }

    public int queryForCount(String sql, Object[] parameters)
            throws DataAccessException {
        sql = "select count(*) from (" + sql + ") t";
        return jdbcTemplate.queryForInt(sql, parameters);
    }

    public void execute(String sql) {
        jdbcTemplate.execute(sql);

    }

    public int[] batchUpdate(String[] sql) {
        return jdbcTemplate.batchUpdate(sql);
    }


}
